#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi



dim_total="
USE jtp_goods_warehouse;
WITH tmp_user AS (
    -- 用户表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_user_profile
    WHERE dt = '${data_date}'
),tmp_review AS (
    -- 商品评价表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_review
    WHERE dt = '${data_date}'
),tmp_sku AS (
    -- 商品SKU表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_sku
    WHERE dt = '${data_date}'
),tmp_product AS (
    -- 商品表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_product
    WHERE dt = '${data_date}'
),tmp_category AS (
    -- 商品类目表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_category
    WHERE dt = '${data_date}'
),tmp_brand AS (
    -- 品牌表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_brand
    WHERE dt = '${data_date}'
),tmp_content AS (
    -- 内容表
    SELECT
        *
    FROM jtp_goods_warehouse.ods_content
    WHERE dt = '${data_date}'
)
INSERT INTO TABLE jtp_goods_warehouse.dim_total PARTITION (dt = '${data_date}')
SELECT
    t1.user_id
    ,t1.gender
    ,t1.age
    ,t1.province
    ,t1.city
    ,t1.price_preference
    ,t1.interest_tags
    ,t2.sku_id
    ,t2.product_id
    ,t3.sku_name
    ,t3.attributes
    ,t3.price
    ,t3.stock
    ,t5.category_id
    ,t5.category_name
    ,t5.parent_category_id
    ,t5.level
    ,t6.brand_id
    ,t6.brand_name
    ,t6.brand_desc
    ,t2.review_id
    ,t2.rating
    ,t2.review_text
    ,t2.review_time
    ,t7.content_id
    ,t7.content_type
    ,t7.content_title
    ,t7.publish_time
    ,t2.product_id IS NULL AND t2.sku_id IS NULL AS is_invalid_traffic
FROM tmp_user t1
LEFT JOIN tmp_review t2 ON t1.user_id = t2.user_id
LEFT JOIN tmp_sku  t3 ON t2.sku_id = t3.sku_id
LEFT JOIN tmp_product t4 ON t2.product_id = t4.product_id
LEFT JOIN tmp_category t5 ON t4.category_id = t5.category_id
LEFT JOIN tmp_brand t6 ON t4.brand_id = t6.brand_id
LEFT JOIN tmp_content t7 ON t3.product_id = t7.product_id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e  "${dim_total}"